﻿/*
 * Copyright(C) 2010,ajayumi 保留所有权利。( All rights reserved. )
 * 
 * 文件名称：SqlAjRoleProvider.cs
 * 摘    要：
 * 当前版本：1.0
 * 作    者：黄乙冬 (ajayumi)
 * 创建日期：2010年10月29日星期二
 */
using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Web.Security;
using System.Data.Common;

namespace ajayumi.Platform.Web.Security
{
    public class SqlAjRoleProvider : RoleProvider
    {
        #region Global connection string, generic exception message, event log info.

        private string eventSource = "SqlAjRoleProvider";
        private string eventLog = "Application";
        private string exceptionMessage = "An exception occurred. Please check the Event Log.";

        private ConnectionStringSettings pConnectionStringSettings;
        private string connectionString;
        private Guid pApplicationId;

        private const string PROVIDER_NAME = "System.Data.SqlClient";
        private DbParameter m_DbParameter = null;
        private static readonly DbProviderFactory m_Factory = DbProviderFactories.GetFactory(PROVIDER_NAME);

        #endregion

        #region Initialize


        /// <summary>
        /// System.Configuration.Provider.ProviderBase.Initialize Method
        /// </summary>
        /// <param name="name"></param>
        /// <param name="config"></param>
        public override void Initialize(string name, NameValueCollection config)
        {

            //
            // Initialize values from web.config.
            //

            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "SqlAjRoleProvider";

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Sql Ajayumi Role provider");
            }

            // Initialize the abstract base class.
            base.Initialize(name, config);


            if (string.IsNullOrWhiteSpace(config["applicationName"]))
            {
                pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            }
            else
            {
                pApplicationName = config["applicationName"];
            }


            if (config["writeExceptionsToEventLog"] != null)
            {
                if (config["writeExceptionsToEventLog"].ToUpperInvariant() == "TRUE")
                {
                    pWriteExceptionsToEventLog = true;
                }
            }


            //
            // Initialize SqlConnection.
            //

            pConnectionStringSettings = ConfigurationManager.
              ConnectionStrings[config["connectionStringName"]];

            if (pConnectionStringSettings == null || string.IsNullOrWhiteSpace(pConnectionStringSettings.ConnectionString))
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            connectionString = pConnectionStringSettings.ConnectionString;

            pApplicationId = this.GetApplicationId( ApplicationName);

        }

        #endregion

        #region System.Web.Security.RoleProvider properties.

        private bool pWriteExceptionsToEventLog = false;
        /// <summary>
        /// If false, exceptions are thrown to the caller. If true,
        /// exceptions are written to the event log.
        /// </summary>
        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }

        private string pApplicationName;
        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        #endregion

        #region System.Web.Security.RoleProvider methods.


        /// <summary>
        /// RoleProvider.AddUsersToRoles
        /// </summary>
        /// <param name="usernames"></param>
        /// <param name="roleNames"></param>
        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            foreach (string rolename in roleNames)
            {
                if (!RoleExists(rolename))
                {
                    throw new ProviderException("Role name not found.");
                }
            }

            foreach (string username in usernames)
            {
                if (username.IndexOf(',') > 0)
                {
                    throw new ArgumentException("User names cannot contain commas.");
                }

                foreach (string rolename in roleNames)
                {
                    if (IsUserInRole(username, rolename))
                    {
                        throw new ProviderException("User is already in role.");
                    }
                }
            }

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("INSERT INTO [aspnet_UsersInRoles] ");
                sql.Append(" (UserId, RoleId) ");
                sql.Append(" Values(@UserId, @RoleId)");
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    SqlParameter userParm = cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
                    SqlParameter roleParm = cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier);

                    SqlTransaction tran = null;

                    try
                    {
                        conn.Open();
                        tran = conn.BeginTransaction();
                        cmd.Transaction = tran;

                        foreach (string username in usernames)
                        {
                            foreach (string rolename in roleNames)
                            {
                                userParm.Value = GetUserId(username);
                                roleParm.Value = GetRoleId(rolename);
                                cmd.ExecuteNonQuery();
                            }
                        }

                        tran.Commit();
                    }
                    catch (SqlException e)
                    {
                        try
                        {
                            tran.Rollback();
                        }
                        catch { }


                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "AddUsersToRoles");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// RoleProvider.CreateRole
        /// </summary>
        /// <param name="roleName"></param>
        public override void CreateRole(string roleName)
        {
            if (roleName.IndexOf(',') > 0)
            {
                throw new ArgumentException("Role names cannot contain commas.");
            }

            if (RoleExists(roleName))
            {
                throw new ProviderException("Role name already exists.");
            }

            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO [aspnet_Roles] ");
            sql.Append("(Rolename, LoweredRoleName, ApplicationId) ");
            sql.Append("Values(@Rolename, @LoweredRoleName, @ApplicationId)");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
                    cmd.Parameters.Add("@LoweredRolename", SqlDbType.NVarChar, 256).Value = roleName.ToLowerInvariant();
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    try
                    {
                        conn.Open();

                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "CreateRole");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }
        }


        /// <summary>
        /// RoleProvider.DeleteRole
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="throwOnPopulatedRole"></param>
        /// <returns></returns>
        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            if (!RoleExists(roleName))
            {
                throw new ProviderException("Role does not exist.");
            }

            if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
            {
                throw new ProviderException("Cannot delete a populated role.");
            }

            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM [aspnet_Roles] ");
            sql.Append("WHERE RoleId = @RoleId AND ApplicationId = @ApplicationId");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = GetRoleId(roleName);
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    sql.Clear();
                    sql.Append("DELETE FROM [aspnet_UsersInRoles] ");
                    sql.Append("WHERE RoleId = @RoleId");

                    using (SqlCommand cmd2 = new SqlCommand(sql.ToString(), conn))
                    {

                        cmd2.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = GetRoleId(roleName);

                        SqlTransaction tran = null;

                        try
                        {
                            conn.Open();
                            tran = conn.BeginTransaction();
                            cmd.Transaction = tran;
                            cmd2.Transaction = tran;

                            cmd2.ExecuteNonQuery();
                            cmd.ExecuteNonQuery();

                            tran.Commit();
                        }
                        catch (SqlException e)
                        {
                            try
                            {
                                tran.Rollback();
                            }
                            catch { }


                            if (WriteExceptionsToEventLog)
                            {
                                WriteToEventLog(e, "DeleteRole");

                                return false;
                            }
                            else
                            {
                                throw;
                            }
                        }
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// RoleProvider.GetAllRoles
        /// </summary>
        /// <returns></returns>
        public override string[] GetAllRoles()
        {
            string tmpRoleNames = "";

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Rolename FROM [aspnet_Roles] ");
            sql.Append("WHERE ApplicationId = @ApplicationId");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {

                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    SqlDataReader reader = null;

                    try
                    {
                        conn.Open();

                        reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            tmpRoleNames += reader.GetString(0) + ",";
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetAllRoles");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }

            if (tmpRoleNames.Length > 0)
            {
                // Remove trailing comma.
                tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
                return tmpRoleNames.Split(',');
            }

            return new string[0];
        }

        /// <summary>
        /// RoleProvider.GetRolesForUser
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public override string[] GetRolesForUser(string username)
        {
            string tmpRoleNames = "";

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Rolename FROM [aspnet_Roles] r, [aspnet_UsersInRoles] ur ");
            sql.Append("WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId and ur.UserId = @UserId ORDER BY RoleName");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = GetUserId(username);
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    try
                    {
                        conn.Open();

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tmpRoleNames += reader.GetString(0) + ",";
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetRolesForUser");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }

            if (tmpRoleNames.Length > 0)
            {
                // Remove trailing comma.
                tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
                return tmpRoleNames.Split(',');
            }

            return new string[0];
        }

        /// <summary>
        /// RoleProvider.GetUsersInRole
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public override string[] GetUsersInRole(string roleName)
        {
            string tmpUserNames = "";

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT u.UserName ");
            sql.Append("FROM   aspnet_Users u, aspnet_UsersInRoles ur ");
            sql.Append("WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId ");
            sql.Append("ORDER BY u.UserName");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    try
                    {
                        conn.Open();
                        Guid roleId = GetRoleId(roleName);
                        cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = roleId;

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tmpUserNames += reader.GetString(0) + ",";
                            }
                        }
                    }

                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetUsersInRole");
                        }
                        else
                        {
                            throw;
                        }
                    }


                }
            }

            if (tmpUserNames.Length > 0)
            {
                // Remove trailing comma.
                tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
                return tmpUserNames.Split(',');
            }

            return new string[0];
        }

        /// <summary>
        /// RoleProvider.IsUserInRole
        /// </summary>
        /// <param name="username"></param>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public override bool IsUserInRole(string username, string roleName)
        {
            bool userIsInRole = false;

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(*) FROM [aspnet_UsersInRoles], [aspnet_Users], [aspnet_Roles] ");
            sql.Append("WHERE [aspnet_UsersInRoles].UserId = [aspnet_Users].UserId AND [aspnet_UsersInRoles].RoleId = aspnet_Roles.RoleId ");
            sql.Append("AND [aspnet_Users].Username = @Username AND [aspnet_Roles].Rolename = @Rolename");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 256).Value = username;
                    cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    try
                    {
                        conn.Open();

                        int numRecs = (int)cmd.ExecuteScalar();

                        if (numRecs > 0)
                        {
                            userIsInRole = true;
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "IsUserInRole");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }
            return userIsInRole;
        }

        /// <summary>
        /// RoleProvider.RemoveUsersFromRoles
        /// </summary>
        /// <param name="usernames"></param>
        /// <param name="roleNames"></param>
        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            foreach (string rolename in roleNames)
            {
                if (!RoleExists(rolename))
                {
                    throw new ProviderException("Role name not found.");
                }
            }

            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    if (!IsUserInRole(username, rolename))
                    {
                        throw new ProviderException("User is not in role.");
                    }
                }
            }

            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM [aspnet_UsersInRoles] ");
            sql.Append("WHERE UserId = @UserId AND RoleId = @RoleId");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {

                    SqlParameter userParm = cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
                    SqlParameter roleParm = cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier);

                    SqlTransaction tran = null;

                    try
                    {
                        conn.Open();
                        tran = conn.BeginTransaction();
                        cmd.Transaction = tran;

                        foreach (string username in usernames)
                        {
                            foreach (string rolename in roleNames)
                            {
                                userParm.Value = GetUserId(username);
                                roleParm.Value = GetRoleId(rolename);
                                cmd.ExecuteNonQuery();
                            }
                        }

                        tran.Commit();
                    }
                    catch (SqlException e)
                    {
                        try
                        {
                            tran.Rollback();
                        }
                        catch { }


                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "RemoveUsersFromRoles");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// RoleProvider.RoleExists
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public override bool RoleExists(string roleName)
        {
            bool exists = false;

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(*) FROM [aspnet_Roles] ");
            sql.Append("WHERE Rolename = @Rolename AND ApplicationId = @ApplicationId");


            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    try
                    {
                        conn.Open();

                        int numRecs = (int)cmd.ExecuteScalar();

                        if (numRecs > 0)
                        {
                            exists = true;
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "RoleExists");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }
            return exists;
        }

        /// <summary>
        /// RoleProvider.FindUsersInRole
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="usernameToMatch"></param>
        /// <returns></returns>
        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            string tmpUserNames = "";

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Username FROM [aspnet_UsersInRoles] ");
            sql.Append("WHERE Username LIKE @UsernameSearch AND Rolename = @Rolename AND ApplicationId = @ApplicationId");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@UsernameSearch", SqlDbType.NVarChar, 256).Value = usernameToMatch;
                    cmd.Parameters.Add("@RoleName", SqlDbType.NVarChar, 256).Value = roleName;
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;

                    try
                    {
                        conn.Open();
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tmpUserNames += reader.GetString(0) + ",";
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "FindUsersInRole");
                        }
                        else
                        {
                            throw;
                        }
                    }
                }
            }

            if (tmpUserNames.Length > 0)
            {
                // Remove trailing comma.
                tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
                return tmpUserNames.Split(',');
            }

            return new string[0];
        }

        //
        // WriteToEventLog
        //   A helper function that writes exception detail to the event log. Exceptions
        // are written to the event log as a security measure to avoid private database
        // details from being returned to the browser. If a method does not return a status
        // or boolean indicating the action succeeded or failed, a generic exception is also 
        // thrown by the caller.
        //
        
        private Guid GetRoleId(string roleName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT  RoleId ");
            sql.Append("FROM  aspnet_Roles ");
            sql.Append("WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
                    cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
                    {
                        try
                        {
                            conn.Open();
                            Guid? result = cmd.ExecuteScalar() as Guid?;
                            if (result.HasValue)
                                return result.Value;
                        }
                        catch (SqlException e)
                        {
                            if (WriteExceptionsToEventLog)
                            {
                                WriteToEventLog(e, "GetRoleId");
                            }
                            else
                            {
                                throw;
                            }
                        }

                    }
                }
            }
            return Guid.Empty;

        }

        private Guid GetUserId(string userName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT  UserId ");
            sql.Append("FROM  aspnet_Users ");
            sql.Append("WHERE LOWER(@UserName) = LoweredUserName AND ApplicationId = @ApplicationId");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
                {
                    cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = pApplicationId;
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = userName;
                    {
                        try
                        {
                            conn.Open();
                            Guid? result = cmd.ExecuteScalar() as Guid?;
                            if (result.HasValue)
                                return result.Value;
                        }
                        catch (SqlException e)
                        {
                            if (WriteExceptionsToEventLog)
                            {
                                WriteToEventLog(e, "GetUserId");
                            }
                            else
                            {
                                throw;
                            }
                        }

                    }
                }
            }
            return Guid.Empty;

        }

        #endregion

        private void WriteToEventLog(SqlException e, string action)
        {
            using (EventLog log = new EventLog())
            {
                log.Source = eventSource;
                log.Log = eventLog;

                string message = exceptionMessage + "\n\n";
                message += "Action: " + action + "\n\n";
                message += "Exception: " + e.ToString();

                log.WriteEntry(message);
            }
        }
        #region Functions 'borrowed' from SqlAjMembershipProvider

        private Guid GetApplicationId(string applicationName)
        {
            using (DbConnection conn = m_Factory.CreateConnection())
            {
                conn.ConnectionString = this.connectionString;
                using (DbCommand cmd = m_Factory.CreateCommand())
                {
                    cmd.CommandText = "SELECT ApplicationId FROM [aspnet_Applications] " +
                          "WHERE ApplicationName = @ApplicationName";
                    cmd.Connection = conn;

                    this.m_DbParameter = m_Factory.CreateParameter();
                    this.m_DbParameter.ParameterName = "@ApplicationName";
                    this.m_DbParameter.Size = 256;
                    this.m_DbParameter.Value = applicationName;
                    cmd.Parameters.Add(this.m_DbParameter);

                    conn.Open();
                    var applicationId = cmd.ExecuteScalar();
                    if (applicationId == null)
                    {
                        throw new System.Configuration.Provider.ProviderException("Unable to find application id for provided application name: " + applicationName);
                    }
                    return (Guid)(applicationId);

                }
            }
        }

        #endregion
    }
}